#!/bin/bash

APP=edu

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi

dim_user_zip="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dim_user_zip partition(dt)
select
    if(new_id is not null,new_id,old_id),
    if(new_id is not null,new_login_name,old_login_name),
    if(new_id is not null,new_nick_name,old_nick_name),
    if(new_id is not null,new_passwd,old_passwd),
    if(new_id is not null,new_real_name,old_real_name),
    if(new_id is not null,new_phone_num,old_phone_num),
    if(new_id is not null,new_email,old_email),
    if(new_id is not null,new_head_img,old_head_img),
    if(new_id is not null,new_user_level,old_user_level),
    if(new_id is not null,new_birthday,old_birthday),
    if(new_id is not null,new_gender,old_gender),
    if(new_id is not null,new_create_time,old_create_time),
    if(new_id is not null,new_operate_time,old_operate_time),
    if(new_id is not null,new_status,old_status),
    if(new_id is not null,new_vip_id,old_vip_id),
    if(new_id is not null,new_from_vip,old_from_vip),
    if(new_id is not null,new_to_vip,old_to_vip),
    if(new_id is not null,new_start_date,old_start_date),
    if(new_id is not null,new_end_date,old_end_date),
    if(new_id is not null,new_end_date,old_end_date) dt
from (
    select
			old.id old_id,
			old.login_name old_login_name,
			old.nick_name old_nick_name,
			old.passwd old_passwd,
			old.real_name old_real_name,
			old.phone_num	old_phone_num,
			old.email	old_email,
			old.head_img	old_head_img,
			old.user_level	old_user_level,
			old.birthday	old_birthday,
			old.gender	old_gender,
			old.create_time	old_create_time,
			old.operate_time	old_operate_time,
			old.status	old_status,
			old.vip_id	old_vip_id,
			old.from_vip	old_from_vip,
			old.to_vip	old_to_vip,
			old.start_date	old_start_date,
			old.end_date	old_end_date,
			new.id new_id,
			new.login_name new_login_name,
			new.nick_name new_nick_name,
			new.passwd new_passwd,
			new.real_name new_real_name,
			new.phone_num	new_phone_num,
			new.email	new_email,
			new.head_img	new_head_img,
			new.user_level	new_user_level,
			new.birthday	new_birthday,
			new.gender	new_gender,
			new.create_time	new_create_time,
			new.operate_time	new_operate_time,
			new.status	new_status,
			new.vip_id	new_vip_id,
			new.from_vip	new_from_vip,
			new.to_vip	new_to_vip,
			new.start_date	new_start_date,
			new.end_date	new_end_date

		from
		(
			select
				id,
				login_name,
				nick_name,
				passwd,
				real_name,
				phone_num,
				email,
				head_img,
				user_level,
				birthday,
				gender,
				create_time,
				operate_time,
				status,
				vip_id,
				from_vip,
				to_vip,
				start_date,
				end_date
			from ${APP}.dim_user_zip
			where dt = '9999-12-31'
		) old
		full outer join
		(
			select
				id,
				login_name,
				nick_name,
				md5(passwd) passwd,
				md5(real_name) real_name,
				md5(phone_num) phone_num,
				md5(email) email,
				head_img,
				user_level,
				birthday,
				gender,
				create_time,
				operate_time,
				status,
				vip_id,
				from_vip,
				to_vip,
				'$do_date' start_date,
				'9999-12-31' end_date
			from
			(
                select
                    a.id,
                    login_name,
                    nick_name,
                    passwd,
                    real_name,
                    phone_num,
                    email,
                    head_img,
                    user_level,
                    birthday,
                    gender,
                    create_time,
                    update_time operate_time,
                    status,
                    b.id vip_id,
                    from_vip,
                    to_vip,
                    row_number() over(partition by a.id order by ts desc) rn
                from (
                    select
                        ts,data.id,data.login_name,data.nick_name,data.passwd,data.real_name,data.phone_num,data.email,data.head_img,data.user_level,data.birthday,data.gender,data.create_time,data.update_time,data.status
                    from ${APP}.ods_user_info_inc
                    where dt = '$do_date' and type = 'bootstrap-insert'
                         ) a
                left join (
                    select
                        data.id,data.user_id,data.from_vip,data.to_vip
                    from ${APP}.ods_vip_change_detail_inc
                    where dt = '$do_date' and type = 'bootstrap-insert'
                    ) b
                on a.id = b.user_id
			) t1
			where t1.rn = 1
		) new
		on old.id = new .id
         )tmp
union all
select
    old_id, old_login_name, old_nick_name, old_passwd, old_real_name, old_phone_num, old_email, old_head_img, old_user_level, old_birthday, old_gender, old_create_time, old_operate_time, old_status, old_vip_id, old_from_vip, old_to_vip, old_start_date,
    cast(date_add('$do_date',-1) as string) old_end_date,
    cast(date_add('$do_date',-1) as string) dt
from (select
			old.id old_id,
			old.login_name old_login_name,
			old.nick_name old_nick_name,
			old.passwd old_passwd,
			old.real_name old_real_name,
			old.phone_num	old_phone_num,
			old.email	old_email,
			old.head_img	old_head_img,
			old.user_level	old_user_level,
			old.birthday	old_birthday,
			old.gender	old_gender,
			old.create_time	old_create_time,
			old.operate_time	old_operate_time,
			old.status	old_status,
			old.vip_id	old_vip_id,
			old.from_vip	old_from_vip,
			old.to_vip	old_to_vip,
			old.start_date	old_start_date,
			new.id new_id
		from
		(
			select
				id,
				login_name,
				nick_name,
				passwd,
				real_name,
				phone_num,
				email,
				head_img,
				user_level,
				birthday,
				gender,
				create_time,
				operate_time,
				status,
				vip_id,
				from_vip,
				to_vip,
				start_date,
				end_date
			from ${APP}.dim_user_zip
			where dt = '9999-12-31'
		) old
		full outer join
		(
			select
				id,
				login_name,
				nick_name,
				md5(passwd) passwd,
				md5(real_name) real_name,
				md5(phone_num) phone_num,
				md5(email) email,
				head_img,
				user_level,
				birthday,
				gender,
				create_time,
				operate_time,
				status,
				vip_id,
				from_vip,
				to_vip,
				'$do_date' start_date,
				'9999-12-31' end_date
			from
			(
                select
                    a.id,
                    login_name,
                    nick_name,
                    passwd,
                    real_name,
                    phone_num,
                    email,
                    head_img,
                    user_level,
                    birthday,
                    gender,
                    create_time,
                    update_time operate_time,
                    status,
                    b.id vip_id,
                    from_vip,
                    to_vip,
                    row_number() over(partition by a.id order by ts desc) rn
                from (
                    select
                        ts,data.id,data.login_name,data.nick_name,data.passwd,data.real_name,data.phone_num,data.email,data.head_img,data.user_level,data.birthday,data.gender,data.create_time,data.update_time,data.status
                    from ${APP}.ods_user_info_inc
                    where dt = '$do_date' and type = 'bootstrap-insert'
                         ) a
                left join (
                    select
                        data.id,data.user_id,data.from_vip,data.to_vip
                    from ${APP}.ods_vip_change_detail_inc
                    where dt = '$do_date' and type = 'bootstrap-insert'
                    ) b
                on a.id = b.user_id
			) t1
			where t1.rn = 1
		) new
		on old.id = new .id) tmp1
where old_id is not null
and new_id is not null;
"

dim_course_full="
with
course as
(
                select
                        id,
                        course_name,
                        subject_id,
                        teacher,
                        publisher_id,
                        chapter_num,
                        origin_price,
                        reduce_amount,
                        actual_price,
                        create_time,
                        update_time,
                        deleted
                from ${APP}.ods_course_info_full
                where dt='$do_date'
),
chapter as
(
                select
                        id,
                        chapter_name,
                        course_id,
                        video_id,
                        is_free
                from ${APP}.ods_chapter_info_full
                where dt = '$do_date'
),
video as
(
                select
                        id,
                        video_name,
                        during_sec
                from ${APP}.ods_video_info_full
                where dt='$do_date'

),
subject as
(
                select
                        id,
                        subject_name,
                        category_id
                from ${APP}.ods_base_subject_info_full
                where dt = '$do_date'

),
category as
(
                select
                        id,
                        name
                from ${APP}.ods_base_category_info_full
                where dt = '$do_date'
)
insert overwrite table ${APP}.dim_course_full partition(dt='$do_date')
select
        course.id,
        course.course_name,
        course.subject_id,
        course.teacher,
        course.publisher_id,
        course.chapter_num,
        course.origin_price,
        course.reduce_amount,
        course.actual_price,
        chapter.id,
        chapter.chapter_name,
        chapter.is_free,
        subject.subject_name,
        subject.category_id,
        category.name,
        chapter.video_id,
        video.video_name,
        video.during_sec,
        course.create_time,
        course.update_time,
        course.deleted
from course
left join chapter on course.id=chapter.course_id
left join subject on course.subject_id = subject.id
left join category on subject.category_id = category.id
left join video on chapter.video_id = video.id;
"

dim_province_full="
insert overwrite table ${APP}.dim_province_full
select
        id,
        name,
        region_id,
        area_code,
        iso_code,
        iso_3166_2,
    dt
from ${APP}.ods_base_province_full
where dt='$do_date';
"
dim_question_full="
with
question_info as
(
                select
                        id,
                        question_txt,
                        chapter_id,
                        course_id,
                        question_type,
                        creat_time,
                        updata_time,
                        publisher,
                        deleted
                from ${APP}.ods_test_question_info_full
                where dt = '$do_date'
),
question_option as
(
                select
                        id,
                        option_txt,
                        question_id,
                        is_correct
                from ${APP}.ods_test_question_option_full
                where dt = '$do_date'
),
paper_question as
(
                select
                        id,
                        paper_id,
                        score
                from ${APP}.ods_test_paper_question_full
                where dt = '$do_date'
),
paper as
(
                select
                        id,
                        paper_title
                from ${APP}.ods_test_paper_full
                where dt = '$do_date'
),
point_question as
(
                select
                        id,
                        point_id,
                        question_id
                from ${APP}.ods_test_point_question_full
                where dt = '$do_date'
),
knowledge_point as
(
                select
                        id,
                        point_txt,
                        point_level
                from ${APP}.ods_knowledge_point_full
                where dt = '$do_date'
)
insert overwrite table ${APP}.dim_question_full partition(dt='$do_date')
select
        question_info.id,
    question_info.question_txt,
    question_info.chapter_id,
    question_info.course_id,
    question_info.question_type,
        question_option.option_txt,
        question_option.is_correct,
        paper_question.paper_id,
        paper_question.score,
        paper.paper_title,
        point_question.point_id,
        knowledge_point.point_txt,
 knowledge_point.point_level,
   question_info.creat_time,
    question_info.updata_time,
    question_info.publisher,
    question_info.deleted
from question_info
left join question_option on question_info.id=question_option.question_id
left join paper_question on question_info.id = question_option.question_id
left join paper on paper_question.paper_id = paper.id
left join point_question on question_info.id = point_question.id
left join knowledge_point on point_question.point_id = knowledge_point.id;
"


case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_course_full")
    hive -e "$dim_course_full"
;;
"dim_province_full")
    hive -e "$dim_province_full"
;;
"dim_question_full")
    hive -e "$dim_question_full"
;;
"all")
    hive -e "$dim_user_zip$dim_course_full$dim_province_full$dim_question_full"
;;
esac
